package com.supermap.dataservice.project.mapper;

import com.supermap.dataservice.project.mapper.entity.Subject;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import java.util.List;
import java.util.Map;

/**
 * @author aqua
 * @description 专家信息
 * @date 2018年11月01 下午 4:01
 * @email 1301584033@qq.com
 */
@Mapper
public interface GetExpertUserListMapper {

    @Select("select t1.id,t2.HeadImage,t1.unit,t1.score,t2.username,t2.mobile,t3.areaname,t5.name as subjectName, t1.unit as departName,t2.Introduction  from t_expertinfo t1\n" +
            "inner join t_user t2\n" +
            "on t1.userid=t2.id and t1.id=#{experId} and t2.del_flg=0 and t1.del_flg=0\n" +
            "inner join t_area t3\n" +
            "on t2.areaCode=t3.areacode \n" +
            "inner join t_expert_subject t4\n" +
            "on t4.expertId=t1.id\n" +
            "inner join t_subject t5\n" +
            "on t5.id=t4.subjectId")
    List<Map<String,Object>> getExpertdetailsByexperId(@Param("experId") String experId);

    @SelectProvider(type = expertInfoSql.class, method = "getExepertBysSubjectIdandAreaCode")
    List<Map<String,Object>> getExepertBysSubjectIdandAreaCode(@Param("subjectId") Integer subjectId,@Param("areaCode")Integer areaCode,@Param("pageSize")Integer pageSize,@Param("pageNum") Integer pageNum);

    @SelectProvider(type = expertInfoSql.class, method = "getExepertBysSubjectIdandAreaCodeCountSum")
    int getExepertBysSubjectIdandAreaCodeCountSum(@Param("subjectId") Integer subjectId,@Param("areaCode")Integer areaCode);


    @Select("select id as subjectId,name as subjectName from t_subject where del_flg=0 order by order_index ")
    List<Subject> getListSubjectDownExpert(@Param("pageSize")Integer pageSize,@Param("pageNum") Integer pageNum);

    @Select("select count(1) from t_subject")
    int getListSubjectDownExpertCountSum();
    class expertInfoSql{
        public String getExepertBysSubjectIdandAreaCode(Map<String,Object> map){
            Integer areaCode = (Integer)map.get("areaCode");
            Integer subjectId = (Integer)map.get("subjectId");
            Integer pageSize = (Integer)map.get("pageSize");
            Integer pageNum = (Integer)map.get("pageNum");
            StringBuffer sql=new StringBuffer();
            sql.append("select t1.id as expertId,t2.username,t2.id as userId,t2.mobile,t2.HeadImage,t3.areaname from t_expertinfo t1\n" +
                    "            inner join t_user t2\n" +
                    "            on t1.userid = t2.id\n" +
                    "            inner join t_area t3\n" +
                    "            on t2.areaCode=t3.areacode and t1.del_flg=0 and t2.del_flg=0");
            if(areaCode!=null){
                sql.append("  and concat(LEFT(t3.areacode,2),RIGHT(t3.areacode,2))  like concat( "+areaCode+",'%')  ");
            }
            sql.append("    inner join  t_expert_subject t4\n" +
                    "            on t4.expertId = t1.id" );
            if(subjectId!=null){
                sql.append("        and t4.subjectId="+subjectId+"");
            }
            sql.append("\n order by t2.HeadImage" );
            if(pageSize!=null&&pageNum!=null){
                sql.append("      limit "+pageSize+","+pageNum+"   ");
            }


            return  sql.toString();
        }

        public String getExepertBysSubjectIdandAreaCodeCountSum(Map<String,Object> map){
            Integer areaCode = (Integer)map.get("areaCode");
            Integer subjectId = (Integer)map.get("subjectId");
            StringBuffer sql=new StringBuffer();
            sql.append("select count(1) from t_expertinfo t1\n" +
                    "            inner join t_user t2\n" +
                    "            on t1.userid = t2.id\n" +
                    "            inner join t_area t3\n" +
                    "            on t2.areaCode=t3.areacode ");
            if(areaCode!=null){
                sql.append("  and concat(LEFT(t3.areacode,2),RIGHT(t3.areacode,2)) like concat( "+areaCode+",'%') ");
            }
            sql.append("    inner join  t_expert_subject t4\n" +
                    "            on t4.expertId = t1.id" );
            if(subjectId!=null){
                sql.append("        and t4.subjectId="+subjectId+"");
            }
            return  sql.toString();
        }
    }


}
